/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package control;

/**
 *
 * @author Jepsens
 */
public class SQLScripts {

    public SQLScripts() {
    }
    
    public String getEntitetsScript(){
        String SQLString;
        SQLString = 
"create database if not exists " +
"set foreign_key_checks = 0;" +
"create table Cargo" +
"(" +
"ID					int(4)		not null auto_increment, " +
"CargoType				varchar(30)	not null, " +
"Price					int(6)		not null, " +
"Lower					int(4)		not null, " +
"Upper					int(4)		not null, " +
"Cargoweight				int(8)		not null, " +
"Primary key (ID)						, " +
"Unique key (CargoType, Lower, Upper,cargoweight) " +
") " +
"engine = innodb; " +

"create table Driver" +
"(" +
"PhoneNo					int(11)		not null," +
"FName					varchar(20)	not null," +
"LName					varchar(20)	not null," +
"Primary key				(phoneno)" +
")" +
"engine = innodb;" +
"" +
"" +
"create table Vehicle" +
"(" +
"ID						int		(4)	not null auto_increment," +
"LicensePlate			varchar(10)	not null," +
"VType					varchar(40)	not null," +
"MaxNumberOfPallets		int(2)				," +
"CubicMeters				int(2)		not null," +
"MaxWeight				int(5)		not null," +
"Primary key				(ID)," +
"Unique key				(LicensePlate)" +
")" +
"engine = innodb;" +
"" +
"" +
"create table Task" +
"(" +
"TaskID					int(4) 	not null auto_increment," +
"DD						int(2)		not null," +
"MM						int(2)		not null," +
"YYYY					int(4)		not null," +
"Description				varchar(255)		," +
"CargoType				varchar(20)	not null," +
"CargoWeight				int(6)		not null," +
"CargoNumber				int(4)		not null," +
"DangCargo				boolean		not null," +
"CargoID					int(4)				," +
"DriverphoneNo			int(11)				," +
"CustomerPhoneNo			int(4)				," +
"PickupAddressID			int(4)				," +
"DeliveryAddressID		int(4)				," +
"DistanceID				int(4)				," +
"VehicleLicense			varchar(10)			," +
"Primary key				(TaskID)			," +
"Unique key				(DriverPhoneNo, DD, MM, YYYY)," +
"Unique key				(VehicleLicense, DD, MM, YYYY)," +
"Foreign key				(CargoID)			references Cargo(ID)," +
"Foreign key 			(DriverPhoneNo) 	references Driver(PhoneNo), " +
"Foreign key 			(CustomerPhoneNo) 	references Customer(PhoneNo)," +
"Foreign key				(pickupAddressID)	references Address(ID)," +
"Foreign key				(deliveryAddressID)	references	Address(ID)," +
"Foreign key				(DistanceID)		references Distance(ID)," +
"Foreign key				(VehicleLicense)	references Vehicle(LicensePlate)" +
"On delete restrict" +
"On update cascade" +
")" +
"engine = innodb;" +
"" +
"create table Certificate" +
"(" +
"ID						int(4)		not null auto_increment," +
"CType					varchar(50)	not null," +
"Primary key 			(ID)" +
");" +
"" +
"create table DriverCertificate" +
"(" +
"DriverPhoneNo			int(11)		not null," +
"CertificateID			int(4)		not null," +
"Primary key 			(Driverphoneno, CertificateID)," +
"Foreign key 			(DriverPhoneNo) references Driver(PhoneNo)," +
"Foreign key 			(CertificateID)	references Certificate(ID)" +
"On update cascade" +
")" +
"engine = innodb;" +
"" +
"" +
"create table Customer" +
"(" +
"ID						int(4)		not null auto_increment, " +
"PhoneNo 				int(8)		not null," +
"CompanyName				varchar(20)	not null, " +
"Address					varchar(20)	not null," +
"Zipcode					varchar(10)	not null," +
"City					varchar(30)	not null," +
"Primary key				(ID)," +
"Unique key				(PhoneNo)" +
")" +
"engine = innodb;" +
"" +
"" +
"create table Distance" +
"(" +
"ID						int(4) 		not null auto_increment," +
"StartDestination		varchar(20)	not null," +
"EndDestination			varchar(20)	not null," +
"Distance				int(5)		not null," +
"Primary key 			(ID)" +
")" +
"engine = innodb;" +
"" +
"" +
"" +
"create table Address" +
"(" +
"ID						int(4)		not null auto_increment," +
"Company					varchar(30)			," +
"Address 				varchar(25)	not null," +
"Zipcode					varchar(10)	not null," +
"City					varchar(25)	not null," +
"District				varchar(25)			," +
"Country					varchar(25)	not null,			" +
"Primary key				(ID)" +
")" +
"engine = innodb;" +
"" +
"create table City" +
"(" +
"Zipcode					varchar(10) not null," +
"City					varchar(30) not null," +
"primary key 			(Zipcode, City)	" +
")" +
"engine = innodb;			" +
"" +
"set foreign_key_checks = 1;";
        return SQLString;
    }
    
    public String getInsertScript(){
        String SQLString;
        SQLString =
"insert into Cargo (CargoType, Price, Lower, Upper, CargoWeight)" +
"values ('pallet',21,1,12,4250)," +
"('pallet',29,13,18,8500)," +
"('pallet',39,19,24,16000)," +
"('pallet',44,25,30,30000)," +
"('cargo',21,1,28,4250)," +
"('cargo',29,29,40,8500)," +
"('cargo',39,41,48,16000)," +
"('cargo',44,49,60,30000)," +
"('special',69,1,42,18000)," +
"('special',85,43,65,35000);" +
"" +
"insert into Driver (PhoneNo, FName, LName)" +
"values (24675620,'Bent','Larsen')," +
"(24675621,'Klaus','Holm')," +
"(24675622,'Peer','Nielsen')," +
"(24675623,'Brian','Andersen')," +
"(24675624,'Johnny','Nielsen')," +
"(24675625,'Anders','Brendstrup')," +
"(24675626,'Lars','Hansen')," +
"(24675627,'Jacob','Riel')," +
"(24675628,'Hans','Christensen')," +
"(24675629,'Bo','Feldtstrøm')," +
"(24675630,'Bent','Thirslund');" +
"" +
"insert into Vehicle (LicensePlate, VType, MaxNumberOfPallets, CubicMeters, MaxWeight)" +
"values ('XM21309','Lastbil med trailer',30,60,30000)," +
"('SX56897','Lastbil med trailer',30,60,30000)," +
"('SL45188','Stor lastbil',24,48,16000)," +
"('TM23916','Mellem lastbil',18,40,8500)," +
"('BG67880','Lille lastbil',12,28,4250)," +
"('SK33984','Lille lastbil',12,28,4250)," +
"('AW14024','Specialfragt m/lille kran',null,42,18000)," +
"('SR36644','Specialfragt m/stor kran',null,65,35000);" +
"" +
"insert into Customer (PhoneNo, CompanyName, Address, Zipcode, City)" +
"values (70807070,'Elgiganten','Vestergårdsvej 4','4700','Næstved')," +
"(22641208,'Nordox AS','Østensjøveien 13','0661','Oslo');" +
"" +
"insert into Certificate (CType)" +
"values ('ADR certificate')," +
"('Crane certificate')," +
"('EU cargocertificate');" +
"" +
"insert into DriverCertificate (DriverPhoneNo, CertificateID)" +
"values(24675620,3)," +
"(24675621,1)," +
"(24675621,3)," +
"(24675622,1)," +
"(24675622,2)," +
"(24675622,3)," +
"(24675623,2)," +
"(24675623,3)," +
"(24675624,1)," +
"(24675624,2)," +
"(24675625,2)," +
"(24675628,1)," +
"(24675628,3)," +
"(24675629,1)," +
"(24675629,2)," +
"(24675629,3)," +
"(24675630,1)," +
"(24675630,2)," +
"(24675630,3);" +
"" +
"" +
"insert into address(Company,address, zipcode, city, district, country)" +
"values ('Nordox AS','Østensjøveien 13', '0661','Oslo', null, 'Norge')," +
"('Liberio','Flottenstrasse 24', '13407','Berlin', 'Reinickendorf', 'Tyskland')," +
"('Elgiganten','Vestergårdsvej 4','4700', 'Næstved', null, 'Danmark')," +
"('Geräte Zentrallager','NotkeStrasse 85', '22607', 'Hamborg', null, 'Tyskland');" +
"" +
"insert into Distance (StartDestination, EndDestination, Distance)" +
"values ('Næstved', 'Esbjerg', 294)," +
"('Næstved', 'Helsingborg', 125)," +
"('Næstved', 'Göteborg', 357)," +
"('Næstved', 'Oslo', 627)," +
"('Næstved', 'Hamborg', 331)," +
"('Næstved', 'Berlin', 435)," +
"('Næstved', 'Amsterdam', 788)," +
"('Næstved', 'Antwerpen', 873)," +
"('Esbjerg', 'Næstved', 294)," +
"('Esbjerg', 'Helsingborg', 394)," +
"('Esbjerg', 'Göteborg', 602)," +
"('Esbjerg', 'Oslo', 640)," +
"('Esbjerg', 'Hamborg', 309)," +
"('Esbjerg', 'Berlin', 584)," +
"('Esbjerg', 'Amsterdam', 761)," +
"('Esbjerg', 'Antwerpen', 852)," +
"('Helsingborg', 'Næstved', 125)," +
"('Helsingborg', 'Esbjerg', 394)," +
"('Helsingborg', 'Oslo', 534)," +
"('Helsingborg', 'Hamborg', 431)," +
"('Helsingborg', 'NBerlin', 535)," +
"('Helsingborg', 'Amsterdam', 887)," +
"('Helsingborg', 'Antwerpen', 973)," +
"('Göteborg', 'Næstved', 357)," +
"('Göteborg', 'Esbjerg', 602)," +
"('Göteborg', 'Hamborg', 640)," +
"('Göteborg', 'Berlin', 744)," +
"('Göteborg', 'Amsterdam', 1096),	" +
"('Göteborg', 'Antwerpen', 1182)," +
"('Oslo', 'Næstved', 627)," +
"('Oslo', 'Esbjerg', 640)," +
"('Oslo', 'Helsingborg', 534)," +
"('Oslo', 'Hamborg', 812)," +
"('Oslo', 'Berlin', 1031)," +
"('Oslo', 'Amsterdam', 1267)," +
"('Oslo', 'Antwerpen', 1352)," +
"('Hamborg', 'Næstved', 331)," +
"('Hamborg', 'Esbjerg', 309)," +
"('Hamborg', 'Helsingborg', 431)," +
"('Hamborg', 'Göteborg', 640)," +
"('Hamborg', 'Oslo', 812)," +
"('Hamborg', 'Amsterdam', 615)," +
"('Hamborg', 'Antwerpen', 700)," +
"('Berlin', 'Næstved', 435)," +
"('Berlin', 'Esbjerg', 584)," +
"('Berlin', 'Helsingborg', 535)," +
"('Berlin', 'Göteborg', 744)," +
"('Berlin', 'Oslo', 1031)," +
"('Berlin', 'Amsterdam', 858)," +
"('Berlin', 'Antwerpen', 921)," +
"('Amsterdam', 'Næstved', 788)," +
"('Amsterdam', 'Esbjerg', 761)," +
"('Amsterdam', 'Helsingborg', 887)," +
"('Amsterdam', 'Göteborg', 1096)," +
"('Amsterdam', 'Oslo', 1267)," +
"('Amsterdam', 'Hamborg', 615)," +
"('Amsterdam', 'Berlin', 858)," +
"('Antwerpen', 'Næstved', 873)," +
"('Antwerpen', 'Esbjerg', 852)," +
"('Antwerpen', 'Helsingborg', 973)," +
"('Antwerpen', 'Göteborg', 1182)," +
"('Antwerpen', 'Oslo', 1352)," +
"('Antwerpen', 'Hamborg', 700)," +
"('Antwerpen', 'Berlin', 921);" +
"" +
"insert into Task (DD, MM, YYYY, Description, CargoType, CargoWeight, CargoNumber, DangCargo, CargoID, DriverphoneNo, CustomerPhoneNo, pickupAddressID, deliveryAddressID, DistanceID, VehicleLicense)" +
"values(29,11,2013,'80 stk AEG Vaskemaskiner','Stykgods',6400,40,0,6,24675620,70807070,4,3,37,'SL45188')," +
"(02,12,2013,'6 paller Højaktivt blegemiddel (Klasse 4.1 FC1)	8 paller Lugtneutraliserende middel (Klasse 4.1 D)	16 paller Silicagel (ikke klassificeret)','Pallefragt',30000,30,1,4,24675621,22641208,1,2,34,'XM21309');" +
"" +
"insert into City (Zipcode, City)" +
"values ('4700', 'Næstved')," +
"('6700', 'Esbjerg')," +
"('25107', 'Helsingborg')," +
"('40010', 'Göteborg')," +
"('0661', 'Oslo')," +
"('22607', 'Hamborg')," +
"('13407', 'Berlin')," +
"('1000', 'Amsterdam')," +
"('2491', 'Antwerpen');";
        return SQLString;
    }
}
